﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESKanbanFunctionBusiness
    {
        uMESCommonBusiness common = new uMESCommonBusiness();

        #region 车间生产看板
        #region 车间月度生产统计
        public DataTable GetProductionStatistics(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("putin");
            dt.Columns.Add("putout");
            dt.Columns.Add("months");
            List<string> listContainerID = new List<string>();
            string strContaienrIDList = string.Empty;
            //获取批次已指派信息
            DataTable dt1 = GetDispatchContaienrInfo(para);
            if (dt1.Rows.Count > 0)
            {
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    if (strContaienrIDList.Contains("'" + dt1.Rows[i]["containerid"].ToString() + "',") == false)
                    {
                        strContaienrIDList += "'" + dt1.Rows[i]["containerid"].ToString() + "',";
                        listContainerID.Add(dt1.Rows[i]["containerid"].ToString());
                    }
                }
                if (strContaienrIDList != "")
                {
                    strContaienrIDList = strContaienrIDList.TrimEnd(',');
                    para.Add("ContainerIDList", strContaienrIDList);
                    //获取批次工序信息
                    DataTable dt2 = GeContaienrSpecInfo(para);
                    //获取批次报工信息
                    DataTable dt3 = GetContainerReportInfo(para);
                    //投入数
                    int intPutIn = 0;
                    //产出数
                    int intPutOut = 0;
                    //月份
                    string[] months = para["Time"].Replace("','", ",").Split(',');
                    for (int i = months.Length - 1; i >= 0; i--)
                    {
                        intPutIn = 0;
                        intPutOut = 0;
                        for (int j = 0; j < listContainerID.Count; j++)
                        {
                            DataRow[] dr = dt2.Select("containerid ='" + listContainerID[j] + "'");
                            if (dr.Length > 0)
                            {
                                //计算投入数
                                DataRow[] dr1 = dt1.Select("containerid ='" + listContainerID[j] + "' and specid ='" + dr[0]["specid"] + "' and dispatchdate1 ='" + months[i] + "'");
                                if (dr1.Length > 0)
                                {
                                    foreach (DataRow dr11 in dr1)
                                    {
                                        if (!string.IsNullOrEmpty(dr11["qty"].ToString()))
                                        {
                                            intPutIn += int.Parse(dr11["qty"].ToString());
                                        }
                                    }
                                }

                                //计算产出数
                                if (dt3.Rows.Count > 0)
                                {
                                    DataRow[] dr2 = dt3.Select("containerid ='" + listContainerID[j] + "' and specid ='" + dr[dr.Length - 1]["specid"] + "' and reportdate1 ='" + months[i] + "'");
                                    if (dr2.Length > 0)
                                    {
                                        foreach (DataRow dr11 in dr2)
                                        {
                                            if (!string.IsNullOrEmpty(dr11["qty"].ToString()))
                                            {
                                                intPutOut += int.Parse(dr11["qty"].ToString());
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["putin"] = intPutIn.ToString();
                        dt.Rows[dt.Rows.Count - 1]["putout"] = intPutOut.ToString();
                        dt.Rows[dt.Rows.Count - 1]["months"] = months[i];
                    }
                }
            }
            return dt;
        }

        //获取已指派的批次信息
        public DataTable GetDispatchContaienrInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select s.factoryid,di.*,to_char(di.dispatchdate,'yyyy-MM') as dispatchdate1
                        from dispatchinfo di left join container c on c.containerid=di.containerid
                        left join spec s on s.specid=di.specid
                        where c.status=1 and di.state=1  and di.dispatchtype=1
                        and c.parentcontainerid is null and c.containername<>c.containerid ");
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                sb.AppendLine(string.Format("AND s.factoryid in ('{0}')", para["FactoryID"]));
            }
            if (para.Keys.Contains("TeamID") && !string.IsNullOrEmpty(para["TeamID"]))
            {
                sb.AppendLine(string.Format("AND s.factoryid in (select factoryid from team where teamid='{0}')", para["TeamID"]));
            }
            if (para.Keys.Contains("Time") && !string.IsNullOrEmpty(para["Time"]))
            {
                sb.AppendLine(string.Format("AND TO_CHAR(di.dispatchdate,'yyyy-MM') in ('{0}')", para["Time"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        //获取批次工序信息
        public DataTable GeContaienrSpecInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select ch.* from containeroperatehistoryinfo ch left join spec s on s.specid=ch.specid where nvl(ch.isskiped,0)=0 ");
            if (para.Keys.Contains("ContainerIDList") && !string.IsNullOrEmpty(para["ContainerIDList"]))
            {
                sb.AppendLine(string.Format("and ch.containerid in ({0})", para["ContainerIDList"]));
            }
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                sb.AppendLine(string.Format("and s.factoryid in ('{0}')", para["FactoryID"]));
            }
            if (para.Keys.Contains("TeamID") && !string.IsNullOrEmpty(para["TeamID"]))
            {
                sb.AppendLine(string.Format("AND s.teamid='{0}'", para["TeamID"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        //获取批次报工信息
        public DataTable GetContainerReportInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select w.*,to_char(w.reportdate,'yyyy-MM') as reportdate1
                        from workreportinfo w left join spec s on s.specid = w.specid
                        where w.reporttype in ('0','1','3') ");
            if (para.Keys.Contains("ContainerIDList") && !string.IsNullOrEmpty(para["ContainerIDList"]))
            {
                sb.AppendLine(string.Format("and w.containerid in ({0})", para["ContainerIDList"]));
            }
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                sb.AppendLine(string.Format("and s.factoryid in ('{0}')", para["FactoryID"]));
            }
            if (para.Keys.Contains("TeamID") && !string.IsNullOrEmpty(para["TeamID"]))
            {
                sb.AppendLine(string.Format("AND s.teamid = '{0}'", para["TeamID"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion

        #region 班组合格率统计
        public DataTable GetQualRateStatisticsInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("hegelv");
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))//车间
            {
                dt.Columns.Add("teamname");
                DataTable dtTeam = common.GetTeamByFactoryId(para["FactoryID"]);
                if (dtTeam.Rows.Count > 0)
                {
                    double intNumerator = 0;//分子
                    double intDenominator = 0;//分母
                    double db1 = 1;
                    DataTable dtFinish = GetContainerSpecfinishInfo(para);
                    for (int i = 0; i < dtTeam.Rows.Count; i++)
                    {
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["hegelv"] = "0";
                        if (dtFinish.Rows.Count > 0)
                        {
                            DataRow[] dr = dtFinish.Select("teamid='" + dtTeam.Rows[i]["teamid"].ToString() + "'");
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (!string.IsNullOrEmpty(dr1["fixqty"].ToString()))//返修数
                                    {
                                        intNumerator += Convert.ToDouble(dr1["fixqty"].ToString());
                                        intDenominator += Convert.ToDouble(dr1["fixqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["scrapqty"].ToString()))
                                    {
                                        intNumerator += Convert.ToDouble(dr1["scrapqty"].ToString());//报废数
                                        intDenominator += Convert.ToDouble(dr1["scrapqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["eligibilityqty"].ToString()))//  合格数
                                    {
                                        intDenominator += Convert.ToDouble(dr1["eligibilityqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["rbqty"].ToString()))//让步使用数量
                                    {
                                        intDenominator += Convert.ToDouble(dr1["rbqty"].ToString());
                                    }
                                }
                                dt.Rows[dt.Rows.Count - 1]["hegelv"] = (Math.Round((db1 - intNumerator / intDenominator) * 100, 2)).ToString();
                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["teamname"] = dtTeam.Rows[i]["teamname"].ToString();
                    }
                }
            }
            if (para.Keys.Contains("TeamID") && !string.IsNullOrEmpty(para["TeamID"]))
            {
                dt.Columns.Add("teamname");
                DataTable dtTeam = common.GetTeamByTeamId(para["TeamID"]);
                if (dtTeam.Rows.Count > 0)
                {
                    double intNumerator = 0;//分子
                    double intDenominator = 0;//分母
                    double db1 = 1;
                    DataTable dtFinish = GetContainerSpecfinishInfo(para);
                    for (int i = 0; i < dtTeam.Rows.Count; i++)
                    {
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["hegelv"] = "0";
                        if (dtFinish.Rows.Count > 0)
                        {
                            DataRow[] dr = dtFinish.Select("teamid='" + dtTeam.Rows[i]["teamid"].ToString() + "'");
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (!string.IsNullOrEmpty(dr1["fixqty"].ToString()))
                                    {
                                        intNumerator += Convert.ToDouble(dr1["fixqty"].ToString());
                                        intDenominator += Convert.ToDouble(dr1["fixqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["scrapqty"].ToString()))
                                    {
                                        intNumerator += Convert.ToDouble(dr1["scrapqty"].ToString());
                                        intDenominator += Convert.ToDouble(dr1["scrapqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["eligibilityqty"].ToString()))
                                    {
                                        intDenominator += Convert.ToDouble(dr1["eligibilityqty"].ToString());
                                    }

                                    if (!string.IsNullOrEmpty(dr1["rbqty"].ToString()))
                                    {
                                        intDenominator += Convert.ToDouble(dr1["rbqty"].ToString());
                                    }
                                }

                                dt.Rows[dt.Rows.Count - 1]["hegelv"] = (Math.Round((db1 - intNumerator / intDenominator) * 100, 2)).ToString();
                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["teamname"] = dtTeam.Rows[i]["teamname"].ToString();
                    }
                }
            }
            if (!para.Keys.Contains("FactoryID") && !para.Keys.Contains("TeamID"))//生产管理部
            {
                dt.Columns.Add("factoryname");
                DataTable dtFactory = common.GetFactoryNames();
                string[] factorynames = dtFactory.AsEnumerable().Select(t => t.Field<string>("DESCRIPTION")).ToArray().Intersect(ConfigurationManager.AppSettings["Factorys"].Split(';')).ToArray();
                List<DataRow> drFactorys = dtFactory.AsEnumerable().Where(t => factorynames.Contains(t["DESCRIPTION"].ToString())).ToList();
                if (drFactorys.Count > 0)
                {
                    double intNumerator = 0;//分子
                    double intDenominator = 0;//分母
                    para.Add("FactoryID", string.Join("','", drFactorys.Select(t => t.Field<string>("FACTORYID")).ToArray()));
                    DataTable dtFinish = GetContainerSpecfinishInfo(para);
                    for (int i = 0; i < drFactorys.Count; i++)
                    {
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["hegelv"] = "0";
                        if (dtFinish.Rows.Count > 0)
                        {
                            DataRow[] dr = dtFinish.Select("factoryid='" + drFactorys[i]["factoryid"].ToString() + "'");
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (!string.IsNullOrEmpty(dr1["fixqty"].ToString()))//返修数
                                    {
                                        intNumerator += Convert.ToDouble(dr1["fixqty"].ToString());
                                        intDenominator += Convert.ToDouble(dr1["fixqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["scrapqty"].ToString()))
                                    {
                                        intNumerator += Convert.ToDouble(dr1["scrapqty"].ToString());//报废数
                                        intDenominator += Convert.ToDouble(dr1["scrapqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["eligibilityqty"].ToString()))//合格数
                                    {
                                        intDenominator += Convert.ToDouble(dr1["eligibilityqty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["rbqty"].ToString()))//让步使用数量
                                    {
                                        intDenominator += Convert.ToDouble(dr1["rbqty"].ToString());
                                    }
                                }
                                dt.Rows[dt.Rows.Count - 1]["hegelv"] = (Math.Round((1 - intNumerator / intDenominator) * 100, 2)).ToString();
                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["factoryname"] = drFactorys[i]["description"].ToString();
                    }
                }
            }
            return dt;
        }

        public DataTable GetContainerSpecfinishInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT csi.CONFIRMDATE,csi.CONFIRMEMPLOYEEID,csi.CONTAINERID,csi.CONTAINERNAME,csi.DESCRIPTION,csi.ELIGIBILITYQTY,csi.ID,csi.MFGORDERNAME,csi.NOTES,csi.OPRNO,csi.PROCESSNO,csi.PRODUCTNAME,csi.QTY,csi.SCRAPQTY,csi.SPECID,csi.UOMID,csi.WORKFLOWID,csi.CONQTY,csi.FIXQTY,csi.RBQTY,s.FACTORYID,s.TEAMID FROM containerspecfinishinfo csi LEFT JOIN spec s ON s.specid=csi.specid WHERE 1=1");
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                sb.AppendLine(string.Format("AND s.factoryid in ('{0}')", para["FactoryID"]));
            }
            if (para.Keys.Contains("Time") && !string.IsNullOrEmpty(para["Time"]))
            {
                sb.AppendLine(string.Format("AND  TO_CHAR(csi.confirmdate,'yyyy-MM') = '{0}'", para["Time"]));
            }
            return OracleHelper.GetDataTable(sb.ToString());
        }
        #endregion

        #region 班组任务完成率
        public DataTable GetFinishRateStatisticsInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("finishRate");
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                dt.Columns.Add("teamname");
                DataTable dtTeam = common.GetTeamByFactoryId(para["FactoryID"]);
                if (dtTeam.Rows.Count > 0)
                {
                    double intNumerator = 0;//分子
                    double intDenominator = 0;//分母
                    //获取已指派批次信息
                    DataTable dtDispatch = GetDispatchContaienrInfo(para);
                    //获取报工信息
                    DataTable dtReport = new DataTable();
                    string strContaienrIDList = string.Empty;
                    if (dtDispatch.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtDispatch.Rows.Count; i++)
                        {
                            if (strContaienrIDList.Contains("'" + dtDispatch.Rows[i]["containerid"].ToString() + "',") == false)
                            {
                                strContaienrIDList += "'" + dtDispatch.Rows[i]["containerid"].ToString() + "',";
                            }
                        }
                        if (strContaienrIDList != "")
                        {
                            strContaienrIDList = strContaienrIDList.TrimEnd(',');
                            para.Remove("ContainerIDList");
                            para.Add("ContainerIDList", strContaienrIDList);
                            dtReport = GetContainerReportInfo(para);
                        }
                    }
                    for (int i = 0; i < dtTeam.Rows.Count; i++)
                    {
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["finishRate"] = "0";
                        if (dtDispatch.Rows.Count > 0)
                        {
                            DataRow[] dr = dtDispatch.Select("teamid='" + dtTeam.Rows[i]["teamid"].ToString() + "'");
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (!string.IsNullOrEmpty(dr1["qty"].ToString()))
                                    {
                                        intDenominator += Convert.ToDouble(dr1["qty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["id"].ToString()))
                                    {
                                        if (dtReport.Rows.Count > 0)
                                        {
                                            DataRow[] drReport = dtReport.Select("dispatchinfoid='" + dr1["id"] + "'");
                                            if (drReport.Length > 0)
                                            {
                                                foreach (DataRow dr2 in drReport)
                                                {
                                                    if (!string.IsNullOrEmpty(dr2["qty"].ToString()))
                                                    {
                                                        intNumerator += Convert.ToDouble(dr2["qty"].ToString());
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                dt.Rows[dt.Rows.Count - 1]["finishRate"] = (Math.Round((intNumerator / intDenominator) * 100, 2)).ToString();
                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["teamname"] = dtTeam.Rows[i]["teamname"].ToString();
                    }
                }
            }
            if (para.Keys.Contains("TeamID") && !string.IsNullOrEmpty(para["TeamID"]))
            {
                dt.Columns.Add("teamname");
                DataTable dtTeam = common.GetTeamByTeamId(para["TeamID"]);
                if (dtTeam.Rows.Count > 0)
                {
                    double intNumerator = 0;//分子
                    double intDenominator = 0;//分母
                    //获取已指派批次信息
                    DataTable dtDispatch = GetDispatchContaienrInfo(para);
                    //获取报工信息
                    DataTable dtReport = new DataTable();
                    string strContaienrIDList = string.Empty;
                    if (dtDispatch.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtDispatch.Rows.Count; i++)
                        {
                            if (strContaienrIDList.Contains("'" + dtDispatch.Rows[i]["containerid"].ToString() + "',") == false)
                            {
                                strContaienrIDList += "'" + dtDispatch.Rows[i]["containerid"].ToString() + "',";
                            }
                        }
                        if (strContaienrIDList != "")
                        {
                            strContaienrIDList = strContaienrIDList.TrimEnd(',');
                            para.Remove("ContainerIDList");
                            para.Add("ContainerIDList", strContaienrIDList);
                            dtReport = GetContainerReportInfo(para);
                        }
                    }
                    for (int i = 0; i < dtTeam.Rows.Count; i++)
                    {
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["finishRate"] = "0";
                        if (dtDispatch.Rows.Count > 0)
                        {
                            DataRow[] dr = dtDispatch.Select("teamid='" + dtTeam.Rows[i]["teamid"].ToString() + "'");
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (!string.IsNullOrEmpty(dr1["qty"].ToString()))
                                    {
                                        intDenominator += Convert.ToDouble(dr1["qty"].ToString());
                                    }
                                    if (!string.IsNullOrEmpty(dr1["id"].ToString()))
                                    {
                                        if (dtReport.Rows.Count > 0)
                                        {
                                            DataRow[] drReport = dtReport.Select("dispatchinfoid='" + dr1["id"] + "'");
                                            if (drReport.Length > 0)
                                            {
                                                foreach (DataRow dr2 in drReport)
                                                {
                                                    if (!string.IsNullOrEmpty(dr2["qty"].ToString()))
                                                    {
                                                        intNumerator += Convert.ToDouble(dr2["qty"].ToString());
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                dt.Rows[dt.Rows.Count - 1]["finishRate"] = (Math.Round((intNumerator / intDenominator) * 100, 2)).ToString();
                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["teamname"] = dtTeam.Rows[i]["teamname"].ToString();
                    }
                }
            }
            if (!para.Keys.Contains("FactoryID") && !para.Keys.Contains("TeamID"))
            {
                dt.Columns.Add("description");
                DataTable dtFactory = common.GetFactoryNames();
                string[] factorynames = dtFactory.AsEnumerable().Select(t => t.Field<string>("DESCRIPTION")).ToArray().Intersect(ConfigurationManager.AppSettings["Factorys"].Split(';')).ToArray();
                List<DataRow> drFactorys = dtFactory.AsEnumerable().Where(t => factorynames.Contains(t["DESCRIPTION"].ToString())).ToList();
                if (drFactorys.Count > 0)
                {
                    double intNumerator = 0;//分子
                    double intDenominator = 0;//分母
                    para.Add("FactoryID", string.Join("','", drFactorys.Select(t => t.Field<string>("FACTORYID")).ToArray()));
                    //获取已指派批次信息
                    DataTable dtDispatch = GetDispatchContaienrInfo(para);
                    //获取报工信息
                    DataTable dtReport = new DataTable();
                    string strContaienrIDList = string.Empty;
                    if (dtDispatch.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtDispatch.Rows.Count; i++)
                        {
                            if (strContaienrIDList.Contains("'" + dtDispatch.Rows[i]["containerid"].ToString() + "',") == false)
                            {
                                strContaienrIDList += "'" + dtDispatch.Rows[i]["containerid"].ToString() + "',";
                            }
                        }
                        if (strContaienrIDList != "")
                        {
                            strContaienrIDList = strContaienrIDList.TrimEnd(',');
                            para.Remove("ContainerIDList");
                            para.Add("ContainerIDList", strContaienrIDList);
                            dtReport = GetContainerReportInfo(para);
                        }
                    }
                    for (int i = 0; i < drFactorys.Count; i++)
                    {
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["finishRate"] = "0";
                        if (dtDispatch.Rows.Count > 0)
                        {
                            DataRow[] dr = dtDispatch.Select("factoryid='" + drFactorys[i]["factoryid"].ToString() + "'");
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (!string.IsNullOrEmpty(dr1["qty"].ToString()))
                                    {
                                        intDenominator += Convert.ToDouble(dr1["qty"].ToString());
                                    }

                                    if (!string.IsNullOrEmpty(dr1["id"].ToString()))
                                    {
                                        if (dtReport.Rows.Count > 0)
                                        {
                                            DataRow[] drReport = dtReport.Select("dispatchinfoid='" + dr1["id"] + "'");
                                            if (drReport.Length > 0)
                                            {
                                                foreach (DataRow dr2 in drReport)
                                                {
                                                    if (!string.IsNullOrEmpty(dr2["qty"].ToString()))
                                                    {
                                                        intNumerator += Convert.ToDouble(dr2["qty"].ToString());
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                dt.Rows[dt.Rows.Count - 1]["finishRate"] = (Math.Round((intNumerator / intDenominator) * 100, 2)).ToString();
                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["description"] = drFactorys[i]["description"].ToString();
                    }
                }
            }
            return dt;
        }
        #endregion

        #region 质量缺陷分析
        public DataTable GetGetQualDefectionAnalysisInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("rejectreasonname");
            dt.Columns.Add("qty");
            dt.Columns.Add("percent");
            dt.Columns.Add("accumulatedpercent");//累计百分比
            DataTable dtReject = GetRejectappinfo(para);
            if (dtReject.Rows.Count > 0)
            {
                double dbTotal = 0;
                for (int i = 0; i < dtReject.Rows.Count; i++)
                {
                    if (!string.IsNullOrEmpty(dtReject.Rows[i]["qty"].ToString()))
                    {
                        dbTotal += Convert.ToDouble(dtReject.Rows[i]["qty"].ToString());
                    }
                }
                DataTable dtFilter = dtReject.DefaultView.ToTable(true, "rejectreasonname", "rejectreasonid");
                if (dtFilter.Rows.Count > 0)
                {
                    double dbQty = 0;
                    for (int i = 0; i < dtFilter.Rows.Count; i++)
                    {
                        dbQty = 0;
                        dt.Rows.Add();
                        dt.Rows[dt.Rows.Count - 1]["qty"] = "0";
                        dt.Rows[dt.Rows.Count - 1]["percent"] = "0";
                        if (!string.IsNullOrEmpty(dtFilter.Rows[i]["rejectreasonid"].ToString()))
                        {
                            if (dtReject.Select("rejectreasonid='" + dtFilter.Rows[i]["rejectreasonid"].ToString() + "'").Length > 0)
                            {
                                foreach (DataRow dr in dtReject.Select("rejectreasonid='" + dtFilter.Rows[i]["rejectreasonid"].ToString() + "'"))
                                {
                                    if (!string.IsNullOrEmpty(dr["qty"].ToString()))
                                    {
                                        dbQty += Convert.ToDouble(dr["qty"].ToString());
                                    }
                                }
                                dt.Rows[dt.Rows.Count - 1]["qty"] = dbQty.ToString();
                                dt.Rows[dt.Rows.Count - 1]["percent"] = Math.Round((dbQty / dbTotal) * 100, 2).ToString();

                            }
                        }
                        dt.Rows[dt.Rows.Count - 1]["rejectreasonname"] = dtFilter.Rows[i]["rejectreasonname"].ToString();
                    }
                    double dbQty1 = 0;
                    dt.DefaultView.Sort = "qty";
                    dt = dt.DefaultView.ToTable();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dt.Rows[i]["accumulatedpercent"] = "0";
                        dbQty1 = 0;
                        for (int j = 0; j < i + 1; j++)
                        {
                            if (!string.IsNullOrEmpty(dt.Rows[j]["qty"].ToString()))
                            {
                                dbQty1 += Convert.ToDouble(dt.Rows[j]["qty"].ToString());
                            }
                        }
                        dt.Rows[i]["accumulatedpercent"] = Math.Round((dbQty1 / dbTotal) * 100, 2).ToString();

                    }
                }
            }
            return dt;
        }

        //获取不合格审理信息
        public DataTable GetRejectappinfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT ri.*,rr.rejectreasonname
                        FROM rejectappinfo ri LEFT JOIN rejectreason rr ON rr.rejectreasonid = ri.rejectreasonid
                        LEFT JOIN spec s ON s.specid = ri.specid
                        WHERE 1 = 1 ");
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                sb.AppendLine(string.Format("AND s.factoryid = '{0}'", para["FactoryID"]));
            }
            if (para.Keys.Contains("Time") && !string.IsNullOrEmpty(para["Time"]))
            {
                sb.AppendLine(string.Format("AND  TO_CHAR(ri.submitdate,'yyyy-MM') = '{0}'", para["Time"]));
            }
            sb.Append("ORDER BY rr.rejectreasonname");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion
        #endregion

        #region 生产问题处理看板

        public uMESPagingDataDTO GetProductionProblemDisposeData1(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);
            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            retR.DBTable.Columns.Add("problemStatus");//问题状态
            retR.DBTable.Columns.Add("DemandDegree");//需求程度
            retR.DBTable.Columns.Add("OverdueTime");//超期时间
            if (retR.DBTable.Rows.Count > 0)
            {
                for (int i = 0; i < retR.DBTable.Rows.Count; i++)
                {
                    //问题状态
                    if (!string.IsNullOrEmpty(retR.DBTable.Rows[i]["status"].ToString()))
                    {
                        if (retR.DBTable.Rows[i]["status"].ToString() == "0")
                        {
                            retR.DBTable.Rows[i]["problemStatus"] = "未处理";
                        }
                        if (retR.DBTable.Rows[i]["status"].ToString() == "5")
                        {
                            retR.DBTable.Rows[i]["problemStatus"] = "处理中";
                        }
                        if (retR.DBTable.Rows[i]["status"].ToString() == "10")
                        {
                            retR.DBTable.Rows[i]["problemStatus"] = "已处理";
                        }
                        if (retR.DBTable.Rows[i]["status"].ToString() == "20")
                        {
                            retR.DBTable.Rows[i]["problemStatus"] = "已关闭";
                        }
                    }
                    //需求程度
                    if (!string.IsNullOrEmpty(retR.DBTable.Rows[i]["problemlevelname"].ToString()))
                    {
                        if (retR.DBTable.Rows[i]["problemlevelname"].ToString() == "一般")
                        {
                            retR.DBTable.Rows[i]["DemandDegree"] = retR.DBTable.Rows[i]["problemlevelname"].ToString() + "(72小时)";
                        }
                        if (retR.DBTable.Rows[i]["problemlevelname"].ToString() == "紧急")
                        {
                            retR.DBTable.Rows[i]["DemandDegree"] = retR.DBTable.Rows[i]["problemlevelname"].ToString() + "(24小时)";
                        }
                        if (retR.DBTable.Rows[i]["problemlevelname"].ToString() == "特急")
                        {
                            retR.DBTable.Rows[i]["DemandDegree"] = retR.DBTable.Rows[i]["problemlevelname"].ToString() + "(12小时)";
                        }
                    }
                    //超期时间
                    if (!string.IsNullOrEmpty(retR.DBTable.Rows[i]["planneddisposedate"].ToString()))
                    {
                        if (Convert.ToDateTime(DateTime.Now.ToString("yyyy/MM/dd")) > Convert.ToDateTime(retR.DBTable.Rows[i]["planneddisposedate"].ToString()))
                        //if (Convert.ToDateTime(retR.DBTable.Rows[i]["disposedate1"].ToString()) > Convert.ToDateTime(retR.DBTable.Rows[i]["planneddisposedate"].ToString()))
                        {
                            TimeSpan ts = DateTime.Now.Subtract(Convert.ToDateTime(retR.DBTable.Rows[i]["planneddisposedate"].ToString()));
                            retR.DBTable.Rows[i]["OverdueTime"] = Math.Round(ts.TotalHours, 4).ToString();
                        }
                        else
                        {
                            retR.DBTable.Rows[i]["OverdueTime"] = "未超期";
                        }
                    }
                }
            }
            return retR;
        }

        public DataTable GetProductionProblemDisposeData2(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("FinishRate");//完成率
            dt.Columns.Add("ReplayRate");//回复率
            dt.Columns.Add("OverdueRate");//超期率

            DataTable dtAll = OracleHelper.GetDataTable(GetSQL_D(para));
            dt.Rows.Add();
            if (dtAll.Rows.Count > 0)
            {
                double dbOverdue = dtAll.Select("planneddisposedate<'" + DateTime.Now.ToString("yyyy/MM/dd") + "'").Length;
                if (dtAll.Select("status IN ('20')").Length > 0)
                {
                    dt.Rows[dt.Rows.Count - 1]["FinishRate"] = Math.Round((Convert.ToDouble(dtAll.Select("status IN ('20')").Length) / Convert.ToDouble(dtAll.Rows.Count)) * 100, 2) + "%";
                }
                else
                {
                    dt.Rows[dt.Rows.Count - 1]["FinishRate"] = "0.00%";
                }
                if (dtAll.Select("status IN ('5','10')").Length > 0)
                {
                    dt.Rows[dt.Rows.Count - 1]["ReplayRate"] = Math.Round((Convert.ToDouble(dtAll.Select("status IN ('5','10')").Length) / Convert.ToDouble(dtAll.Rows.Count)) * 100, 2) + "%";
                }
                else
                {
                    dt.Rows[dt.Rows.Count - 1]["ReplayRate"] = "0.00%";
                }
                if (dtAll.Select("planneddisposedate<'" + DateTime.Now.ToString("yyyy/MM/dd") + "'").Length > 0)
                {
                    if (dtAll.Rows.Count - dtAll.Select("status IN ('20')").Length>0)//除数不能为0
                    { 
                        dt.Rows[dt.Rows.Count - 1]["OverdueRate"] = Math.Round((dbOverdue / Convert.ToDouble(dtAll.Rows.Count - dtAll.Select("status IN ('20')").Length)) * 100, 2) + "%";
                    }
                    else
                    {
                        dt.Rows[dt.Rows.Count - 1]["OverdueRate"] = "0.00%";
                    }
                }
                else
                {
                    dt.Rows[dt.Rows.Count - 1]["OverdueRate"] = "100.00%";
                }
            }
            else
            {
                dt.Rows[0]["FinishRate"] = "0.00%";
                dt.Rows[0]["ReplayRate"] = "0.00%";
                dt.Rows[0]["OverdueRate"] = "0.00%";
            }
            return dt;
        }
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append(@" SELECT e1.fullname AS submitName,e2.fullname AS toName, e3.fullname AS disposeName,pt.problemtypename,pll.problemlevelname,
                                       pi.*,f.factoryname,to_char(NVL(pi.disposedate,SYSDATE),'yyyy/MM/dd') AS  disposedate1
                                FROM Probleminfo pi
                                LEFT JOIN employee e1 ON e1.employeeid = pi.submitemployeeid
                                LEFT JOIN employee e2 ON e2.employeeid = pi.toemployeeid --要求处理人
                                LEFT JOIN employee e3 ON e3.employeeid = pi.disposeemployeeid --实际处理人
                                LEFT JOIN factory f ON f.factoryid = pi.tofactoryid --要求处理部门
                                LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e1.sessionvaluesid
                                LEFT JOIN problemtype pt ON pt.problemtypeid = pi.problemtypeid
                                LEFT JOIN problemlevel pll ON pll.problemlevelid = pi.problemlevelid
                                WHERE 1 = 1 ");
            if (para.Keys.Contains("FactoryID") && !string.IsNullOrEmpty(para["FactoryID"]))
            {
                strQuery.AppendLine(string.Format("AND sv.factoryid = '{0}'", para["FactoryID"]));
            }
            if (para.Keys.Contains("Time") && !string.IsNullOrEmpty(para["Time"]))
            {
                strQuery.AppendLine(string.Format("AND  TO_CHAR(pi.submitdate,'yyyy-MM') = '{0}'", para["Time"]));
            }
            strQuery.AppendLine(" ORDER BY pi.submitdate");
            return strQuery.ToString();
        }
        #endregion

        #region 获取工时
        /// <summary>
        /// 获取工时
        /// </summary>
        /// <param name="factoryid">分厂ID</param>
        /// <param name="teamid">班组ID</param>
        /// <param name="month">月份，如:2021-01</param>
        /// <returns></returns>
        public DataTable GetWorkReport(string factoryid, string teamid, string month)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sum(ReportHours) ReportHours,teamid,teamname,factoryid,factoryname,factorydesc,reportmonth from (");
            strSql.Append("select nvl(re.workhours,0) ReportHours,t.teamid,t.teamname,fa.factoryid,fa.factoryname,fa.description factorydesc,to_char(wt.reportdate,'yyyy-mm') reportmonth");
            strSql.Append(" from workreportinfo wt left join reportemployeeinfo re on re.reportinfoid=wt.workreportinfoid");
            strSql.Append(" left join dispatchinfo di on di.id=wt.dispatchinfoid left join team t on t.teamid=di.teamid");
            strSql.Append(" left join factory fa on fa.factoryid=t.factoryid where 1=1");
            if (!string.IsNullOrEmpty(factoryid))
            {
                strSql.Append(" and fa.factoryid='" + factoryid + "'");
            }
            if (!string.IsNullOrEmpty(teamid))
            {
                strSql.Append(" and t.teamid='" + teamid + "'");
            }
            if (!string.IsNullOrEmpty(month))
            {
                strSql.Append(" and wt.reportdate>=to_date('" + month + "-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and wt.reportdate<to_date('" + DateTime.Parse(month + "-01 00:00:00").AddMonths(1) + "','yyyy-mm-dd hh24:mi:ss')");
            }
            strSql.Append(" group by re.workhours,t.teamid,t.teamname,fa.factoryid,fa.factoryname,fa.description,wt.reportdate) t");
            strSql.Append(" group by teamid,teamname,factoryid,factoryname,factorydesc,reportmonth");
            DataTable dt = OracleHelper.GetDataTable(strSql.ToString());
            return dt;
        }
        #endregion
    }
}
